from typing import Optional, Tuple, List
from sqlalchemy import func, case, extract, select, literal, or_
from sqlalchemy.orm import Session, joinedload
from app.models.iaro_coop_admin.sponsor import (
    Sponsor, SponsorContact, Sponsorship, SponsorshipPlaces,
    SponsorGroup, SponsorGroupList
)
from app.models.iaro_coop_admin.program import Program
from app.models.coop_app.program_lookup import ProgramLookup
from app.models.coop_app.scholarship_amount import ScholarshipAmount
from app.models.iaro_career.scholars import Scholar
from app.models.iaro_career.ite_detail import ITEDetail
from app.models.iaro_career.scholar_status import ScholarStatus 

class SponsorsRecordRepo:
    def __init__(self, db: Session):
        self.db = db

    def get_records_paginated(
        self,
        year_from: int,
        year_to: int,
        only_current: bool,
        program_id: Optional[int] = None,
        program_code: Optional[List[str]] = None,
        keyword: Optional[str] = None, 
        faculty: Optional[List[str]] = None,
        coop_program: Optional[List[str]] = None,
        page: int = 1,
        page_size: int = 10,
    ) -> Tuple[int, List[dict]]:
        total_amount_sq = (
            select(func.sum(ScholarshipAmount.amount))
            .where(
                ScholarshipAmount.year.between(
                    Program.start_year,
                    Program.start_year + 3
                )
            )
            .correlate(Program) 
            .scalar_subquery()
        )
        
        # Build base query
        q = (
            self.db.query(
                Sponsor.sponsor_id.label("sponsor_id"),
                Sponsor.sponsor_code.label("company_code"),
                Sponsor.sponsor_name.label("company_name"),
                SponsorGroupList.sponsor_group_code.label("umbrella_code"),
                SponsorGroupList.sponsor_group_name.label("umbrella_name"),
                Program.program_id.label("program_id"),
                Program.program_code.label("program_code"),
                func.listagg(func.distinct(Sponsorship.year), ', ')
                    .within_group(Sponsorship.year).label("scholarship_years"),
                func.max(Sponsorship.year).label("latest_year"),
                case(
                    (func.max(Sponsorship.year) > extract('year', func.sysdate()), 'Current'),
                    else_='Not Current'
                ).label("status"),
                Program.start_year.label("program_year"),
                Program.program_length,
                Program.program_value_estimate.label("program_value"),
                ProgramLookup.faculty,
                ProgramLookup.co_op_program.label("coop_program"),
                (func.count(SponsorshipPlaces.sponsorship_place_id) / 4).label("position"),
                total_amount_sq.label("total_amount_per_position"),
                (
                    (func.count(SponsorshipPlaces.sponsorship_place_id) / 4) * total_amount_sq
                ).label("sponsorship_cost"),
                literal('').label("logo"),
                literal('').label("logo_last_updated"),
            )
            .join(SponsorGroup, SponsorGroup.sponsor_id == Sponsor.sponsor_id, isouter=True)
            .join(SponsorGroupList, SponsorGroupList.sponsor_group_id == SponsorGroup.sponsor_group_id, isouter=True)
            .join(Sponsorship, Sponsorship.sponsor_id == Sponsor.sponsor_id, isouter=True)
            .join(Program, Program.program_id == Sponsorship.program_id, isouter=True)
            .join(SponsorshipPlaces, SponsorshipPlaces.sponsorship_id == Sponsorship.sponsorship_id, isouter=True)
            .join(ProgramLookup, ProgramLookup.program_lookup_code == func.substr(Program.program_code, 1, 3), isouter=True)
            .join(ScholarshipAmount, ScholarshipAmount.year == Sponsorship.year, isouter=True)
        )
  
        # Apply filters
        q = q.filter(Sponsorship.year.between(year_from, year_to))
        current_year = extract('year', func.sysdate())
        if only_current is True:
            q = q.having(func.max(Sponsorship.year) > current_year)
        elif only_current is False:
            q = q.having(func.max(Sponsorship.year) <= current_year)
        if program_id is not None:
            q = q.filter(Program.program_id == program_id)
        if program_code:
            q = q.filter(
                or_(*[Program.program_code.ilike(f"%{code.strip()}%") for code in program_code])
            )
        if faculty:
            q = q.filter(ProgramLookup.faculty.in_(faculty))
        if coop_program:
            q = q.filter(ProgramLookup.co_op_program.in_(coop_program))

        if keyword:
            pattern = f"%{keyword}%"
            q = q.filter(or_(
                Sponsor.sponsor_name.ilike(pattern),
                Sponsor.sponsor_code.ilike(pattern),
                SponsorGroupList.sponsor_group_name.ilike(pattern),
                Program.program_code.ilike(pattern),
                ProgramLookup.faculty.ilike(pattern),
                ProgramLookup.co_op_program.ilike(pattern),
            ))

        q = q.group_by(
            Sponsor.sponsor_id,
            Sponsor.sponsor_code,
            Sponsor.sponsor_name,
            SponsorGroupList.sponsor_group_code,
            SponsorGroupList.sponsor_group_name,
            Program.program_id,
            Program.program_code,
            Program.start_year,
            Program.program_length,
            Program.program_value_estimate,
            ProgramLookup.faculty,
            ProgramLookup.co_op_program,
        )

        # Calculate total count using subquery to avoid q.count() binding issues
        subq = q.with_entities(Sponsor.sponsor_id).subquery()
        total = self.db.scalar(select(func.count()).select_from(subq))

        # Paginated query
        rows = q.offset((page - 1) * page_size).limit(page_size).all()
        return total, [r._asdict() for r in rows] # type: ignore

